﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace idataplatform
{
    public partial class hylcounting : Form
    {
        public hylcounting()
        {
            InitializeComponent();
        }

        private void hylcounting_Load(object sender, EventArgs e)
        {
            conncls conns = new conncls();
            string chgedateSQL = "SELECT DATE_FORMAT(MAX(jftime),'%Y年%c月%d日') FROM jfchange_ipark;";
            string chgsdateSQL = "SELECT DATE_FORMAT(DATE_ADD(MAX(jftime),interval -1 day),'%Y年%c月%d日') FROM jfchange_ipark;";
            dateTimePicker1.Text = conns.varStr(chgsdateSQL);
            dateTimePicker2.Text = conns.varStr(chgedateSQL);
            label4.Text = "备注：活跃会员人数：指3个月内有过1次及以上消费积分、或积分换礼、或积分抵扣\n停车或线上签到、或线下核销券的会员人数。";
            //MessageBox.Show(dateTimePicker1.Value.ToShortDateString());
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //MessageBox.Show(dateTimePicker2.Value.ToShortDateString());
            conncls conns = new conncls();
            string hycSQL = "select COUNT(HYHYNO) AS YXHYNO from(select DISTINCT(hyno) AS HYHYNO from qhxtab_ipark where HXTIME >= '" + dateTimePicker1.Value.ToShortDateString() + "' AND HXTIME <= '" + dateTimePicker2.Value.AddDays(1).ToShortDateString() + "' UNION select DISTINCT(HYNO) AS HYHYNO FROM jfchange_ipark where jftime>= '" + dateTimePicker1.Value.ToShortDateString() + "' AND  jftime<='"+ dateTimePicker2.Value.AddDays(1).ToShortDateString() + "' AND jfmark<>'新用户奖励积分') as untable;";
            string nmnoSQL = "SELECT SUM(NMNO) FROM ckdata_ipark WHERE ckdate<='"+ dateTimePicker2.Value.ToShortDateString() + "';";
            string csnoSQL = "select count(hyno) as chyno from hydetail_ipark where ctime<='" + dateTimePicker2.Value.AddDays(-730).ToShortDateString() + "' and hyno not in (SELECT t2.HYNO FROM(SELECT t1.HYNO, t1.PHONENO, ROW_NUMBER() OVER(PARTITION BY t1.HYNO) Num FROM (SELECT HYNO, PHONENO FROM jfchange_ipark where jfmark <> '新用户奖励积分' AND jfmark <> '测试' AND HYNO <> '' UNION ALL SELECT HYNO, PHONENO FROM qhxtab_ipark where HYNO <> '') t1) t2 WHERE t2.Num = 1)";
            //MessageBox.Show(dateTimePicker2.Value.AddDays(-730).ToShortDateString());
            int hynmno = conns.varInt(hycSQL);
            int sumhyno= conns.varInt(nmnoSQL);
            int cshyno = conns.varInt(csnoSQL);
            textBox1.Text = Math.Round((Convert.ToDouble(hynmno) / (Convert.ToDouble(sumhyno)- Convert.ToDouble(cshyno))), 4).ToString("0.00%");
            textBox2.Text = hynmno.ToString();
            textBox3.Text = sumhyno.ToString();
            textBox4.Text = cshyno.ToString();
        }
    }
}
